package com.jueban.repository;

import com.jueban.model.ApplyOfIP;
import com.jueban.model.user.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;


@Repository
public interface ApplyOfIPRepository extends CrudRepository<ApplyOfIP, Integer> {


    Page<ApplyOfIP> findApplyOfIPSByApplicant_DepartmentEquals(Pageable pageable, String department);

    @Query(value = "select a from ApplyOfIP as a left join a.state left join fetch a.applyInfo i where a.state.hashCode = :state " +
            "and a.applicant.department like %:department% " +
            "and (a.finallyIPName like %:ipName% or a.applyInfo.applyIPName like %:ipName%) " +
            "and a.applicant.name like %:applicantName%",countQuery = "select count(a) from ApplyOfIP as a left join a.state left join a.applyInfo i where a.state.hashCode = :state " +
            "and a.applicant.department like %:department% " +
            "and (a.finallyIPName like %:ipName% or a.applyInfo.applyIPName like %:ipName%) " +
            "and a.applicant.name like %:applicantName%")
    Page<ApplyOfIP> findApplyOfIPSWithState(
            @Param("page") Pageable pageable, @Param("state") int state
            , @Param("department") String department, @Param("ipName") String ipName
            , @Param("applicantName") String applicantName);

    @Query(value = "select a from ApplyOfIP as a left join fetch a.applyInfo i where a.applicant.department like %:department% " +
            "and (a.finallyIPName like %:ipName% or a.applyInfo.applyIPName like %:ipName%) " +
            "and a.applicant.name like %:applicantName%",countQuery = "select count(a) from ApplyOfIP as a left join a.applyInfo i where a.applicant.department like %:department% " +
            "and (a.finallyIPName like %:ipName% or a.applyInfo.applyIPName like %:ipName%) " +
            "and a.applicant.name like %:applicantName%")
    Page<ApplyOfIP> findApplyOfIPS(
            @Param("page") Pageable pageable, @Param("department") String department
            , @Param("ipName") String ipName, @Param("applicantName") String applicantName);

    Page<ApplyOfIP> findApplyOfIPSByApplicant(Pageable pageable, User user);

    @Query(value = "select a from ApplyOfIP as a left join fetch a.applyInfo i where a.applicant = :user " +
            "and (a.finallyIPName like %:ipName% or a.applyInfo.applyIPName like %:ipName%)",countQuery = "select count(a) from ApplyOfIP as a left join a.applyInfo i where a.applicant = :user " +
            "and (a.finallyIPName like %:ipName% or a.applyInfo.applyIPName like %:ipName%)")
    Page<ApplyOfIP> findApplyOfIPSByApplicantQuery(@Param("page") Pageable pageable,@Param("user") User user
            ,@Param("ipName") String ipName);


    @Query(value = "select a from ApplyOfIP as a left join fetch a.applyInfo i where a.applicant = :user and a.state.hashCode = :state and a.id not in (select r.applyOfIP.id from ApplyReward as r)"
            ,countQuery = "select count(a) from ApplyOfIP as a left join a.applyInfo i where a.applicant = :user and a.state.hashCode = :state and a.id not in (select r.applyOfIP.id from ApplyReward as r)")
    Page<ApplyOfIP> findApplyOfIPSByApplicantWithState(Pageable pageable,@Param("user") User user,@Param("state") int state);


}
